#use encoding 'utf-8';
$dbname =shift;
$maindir=shift;
binmode STDOUT, ":utf8";
use DBI;
use CGI;
#use strict;
#print "arguments @argv @argc \n";
my #$dbname=$argv[0];
$dbistring ="dbi:ODBC:$dbname";
#$##maindir = $argv[1];
print "dbname $dbname $maindir\n";

my $dbh= DBI->connect ($dbistring,'','') || die "could not connect $dbistring\n";

#
#
#
=head
my $query="SELECT UP40MustRoll0910.Panchayat_Code, PANCHAYATS.Panchayat_Name, UP40MustRoll0910.Work_Code, UPWork_detail.work_name, UP40MustRoll0910.Tot_Attend, UP40MustRoll0910.Wage_Per_day, UP40MustRoll0910.amt_paid_measure, UP40MustRoll0910.MSR_No
FROM ((UP40Musterrollmaster0910 INNER JOIN (UP40MustRoll0910 INNER JOIN UPWork_detail ON (UP40MustRoll0910.Block_code = UPWork_detail.block_code) AND (UP40MustRoll0910.Panchayat_Code = UPWork_detail.Panchayat_code) AND (UP40MustRoll0910.Work_Code = UPWork_detail.work_code)) ON (UP40Musterrollmaster0910.Block_code = UP40MustRoll0910.Block_code) AND (UP40Musterrollmaster0910.MSR_No = UP40MustRoll0910.MSR_No) AND (UP40Musterrollmaster0910.Work_Code = UP40MustRoll0910.Work_Code)) INNER JOIN PANCHAYATS ON UP40MustRoll0910.Panchayat_Code = PANCHAYATS.Panchayat_Code) INNER JOIN Exe_Agency ON UPWork_detail.Exe_Agency_code = Exe_Agency.Exe_Agency_code
ORDER BY UP40MustRoll0910.Panchayat_Code, UP40MustRoll0910.Work_Code, UP40MustRoll0910.Tot_Attend DESC , UP40MustRoll0910.MSR_No DESC";
****/
=cut
my $query1="select distinct Panchayats.district_code,Panchayats.block_code,Panchayats.panchayat_code,Districts.district_name,Blocks.block_name,Panchayats.panchayat_name from 
(Panchayats inner join Districts 
on districts.district_code=panchayats.district_code)
inner join blocks  
on blocks.block_code=panchayats.block_code";
my $sth=$dbh->prepare($query1);
$sth->execute();
my $district=0;
my $block=0;
my $panchayat=0;
my $count=0;
my %panchayats; #hash of hashes{ district_code=>{block_code=>(panchayat1,panchayat2,....),...},....}
%districts;
%blocks;
%panchayatnames;
while (@ary=$sth->fetchrow_array ()) {
 $district=($ary[0]!=$district) ? $ary[0]:$district;
 $block=($ary[1]!=$block) ? $ary[1]:$block;
 $panchayat=($ary[2]!=$panchayat) ? $ary[2]:$panchayat;
 $panchayatnames{$panchayat}=$ary[5];
 $districts{$district}=$ary[3];
 $blocks{$block}=$ary[4];
 #print "$count : $district $block $panchayat \n";
 $count++;
  #if (exists $panchayats[$block]){
  push @{$panchayats{$district}{$block}},$panchayat;
  #} else {
  #$panchayats{$block}=["$panchayat"];
  #}
 
}
=head
print %panchayats;
use Data::Dumper;
print Dumper(%panchayats);
=cut
$count=1;
#$maindir="c:/nregaout1";
		my $query_0910="SELECT distinct   UPWork_detail.work_name,UP40MustRoll0910.Tot_Attend,  UP40MustRoll0910.amt_paid_measure, 
		UP40MustRoll0910.MSR_No,UP40Registration.reg_no,UP40Registration.head_of_household,UP40Registration.father_or_husband_name,UPWork_detail.work_code
FROM (UP40MustRoll0910 INNER JOIN UPWork_detail on 
 UP40MustRoll0910.Work_Code = UPWork_detail.work_code ) INNER JOIN UP40Registration on UP40Registration.reg_no=UP40MustRoll0910.reg_no
 
WHERE  UP40MustRoll0910.panchayat_code=?";
#GROUP BY UPWork_detail.work_name,UP40MustRoll0910.MSR_No";
my $query_0809="SELECT   distinct UPWork_detail.work_name,UP40MustRoll0809.Tot_Attend,  UP40MustRoll0809.amt_paid_measure, 
		UP40MustRoll0809.MSR_No,UP40Registration.reg_no,UP40Registration.head_of_household,UP40Registration.father_or_husband_name,UPWork_detail.work_code
FROM (UP40MustRoll0809 INNER JOIN UPWork_detail on 
 UP40MustRoll0809.Work_Code = UPWork_detail.work_code ) INNER JOIN UP40Registration on UP40Registration.reg_no=UP40MustRoll0809.reg_no
 
WHERE  UP40MustRoll0809.panchayat_code=?";

#GROUP BY UPWork_detail.work_name,UP40MustRoll0809.MSR_No";
print "preparing database \n";
$sth0910 = $dbh->prepare($query_0910);
$sth0809=$dbh->prepare($query_0809);

foreach $district(keys(%panchayats)) {
my $distdir = $maindir."/".$districts{$district};
mkdir $distdir;
  foreach $block(keys %{$panchayats{$district}}) {
   $blockdir= $distdir."/".$blocks{$block};
   if (! -d $blockdir) { mkdir $blockdir;}
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
		$panchayatdir= $blockdir."/".$panchayatnames{$panchayat};
		#if (! -d $panchayat) { mkdir $panchayatdir;}

#print ".";
		my %works;
		$sth0809->execute($panchayat);
		$sth0910->execute($panchayat);
		while (my @arry1=$sth0809->fetchrow_array()){
			$reg_no=$arry1[4];
			$workcode=$arry1[7];
		#	if ($#{$works{$reg_no}{'0809'}{$workcode}{'msr'}}% 8==7) {push @{$works{$reg_no}{'0809'}{$workcode}{'msr'}},"<br>";}
			
			push @{$works{$reg_no}{'0809'}{$workcode}{'msr'}},"$arry1[3](".$arry1[1].")";
			$works{$reg_no}{'0809'}{$workcode}{'tot_attend'}+=$arry1[1];
			$works{$reg_no}{'0809'}{$workcode}{'tot_paid'}+=$arry1[2];
			$works{$reg_no}{'0809'}{$workcode}{'workname'}=$arry1[0];
			$works{$reg_no}{'household'}=$arry1[5]."-".$arry1[6];
		}
	#$sth0910->execute($reg_no);
		while (my @arry1=$sth0910->fetchrow_array()){
			$reg_no=$arry1[4];
			$workcode=$arry1[7];
			#if ($#{$works{$reg_no}{'0910'}{$workcode}{'msr'}}% 8==7) {push @{$works{$reg_no}{'0910'}{$workcode}{'msr'}},"<br>";}
			push @{$works{$reg_no}{'0910'}{$workcode}{'msr'}},"$arry1[3](".$arry1[1].")";
			$works{$reg_no}{'0910'}{$workcode}{'tot_attend'}+=$arry1[1];
			$works{$reg_no}{'0910'}{$workcode}{'tot_paid'}+=$arry1[2];
			$works{$reg_no}{'0910'}{$workcode}{'workname'}=$arry1[0];
			$works{$reg_no}{'household'}=$arry1[5]."-".$arry1[6];
		}
use Data::Dumper;
print Dumper(%{$works{'UP-40-001-013-001/30'}});
print "\n prepared..now writing files \n";
print $blockdir,"/$panchayatnames{$panchayat}\n";
#exit;
if (! -d "$blockdir/workers") {mkdir "$blockdir/workers" || die "could not make directory $blockdir/$workers\n";}
open $fh,"> $blockdir/workers_".$panchayatnames{$panchayat}.".html" || die "could not open file \n";
binmode $fh ,":utf8";

#print $fh "Content-type:text/html;charset=utf-8;\n\n";
#print $fh "Content-type: text/html;charset=utf-8";

#('content="text/html; charset=utf-8"');
print $fh qq(<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Work details</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="robots" content="index, follow" />

<link rel="stylesheet" href="../styles.css" type="text/css" />
 </head>);
		#print $fh qq(<head> <link rel=stylesheet href="../../../style.css"></head>);
		print $fh qq(<body style="margin: 0px; padding: 0px;">);
		print $fh qq(<h3 align=center> $panchayatnames{$panchayat}: Workers  in financial year 2008-09 & 2009-10 </h3>);

		print $fh qq(<table  style="height: 100%;width:100%;margin-top:40;" align=center cellpadding="10" cellspacing="0" border="1" >\n); # manipulte to look like you want it
 
		print $fh "<thead>";
		print $fh "<th>Sl.No.</th><th>Name of Worker</th><th>Financial Year</th><th>Work(Muster roll_no(no_of_days)</th><th>Total Attend</th><th>Total Paid</th>";
		print $fh "</thead>";
		$count=1;
		foreach my $worker(sort sortworkercode keys %works) {
			
			$tot_attend=0;
			$tot_paid=0;
			#@mustrolls=();
			print $fh "<tr> \n";
			
			
						#print ".";
			if ($works{$worker}{'0809'} || $works{$worker}{'0910'} ) {
					print $fh "  <td rowspan=2><font size=-1>\n";
					print $fh "		$count\n";
					print $fh "	 </font></td> \n";
					
					print $fh "  <td rowspan=2>\n";
					print $fh "		<font size=-1>$works{$worker}{'household'} <br>[$worker]</font>\n";
					print $fh "	 </td> \n";
					print $fh "<td><font size=-1>";
					print $fh "2008-2009";
					print $fh "</font></td>";
					#print $fh "<tr>";
					$tot_attend=$tot_paid=0;
					print $fh "  <td width=100><font size=-1>\n";
					foreach my $key(keys %{$works{$worker}{'0809'}}){
						
						$workname =$works{$worker}{'0809'}{$key}{'workname'};
						print $fh "$workname".join(',',@{$works{$worker}{'0809'}{$key}{'msr'}})."\n";
						$tot_attend += $works{$worker}{'0809'}{$key}{'tot_attend'};
						$tot_paid   += $works{$worker}{'0809'}{$key}{'tot_paid'};
					
					
					}
					print $fh "	</font> </td> \n";
					print $fh "  <td><font size=-1>\n";
					print $fh "		$tot_attend\n";
					print $fh "	 </font></td> \n";
					print $fh "  <td><font size=-1>\n";
					print $fh "		$tot_paid\n";
					print $fh "	 </font></td> \n";
					print $fh "</tr> \n";
					print $fh "<tr>";
					print $fh "<td><font size=-1>";
					print $fh "2009-2010";
					print $fh "</font></td>";
					print $fh "  <td><font size=-1>\n";
					$tot_attend=$tot_paid=0;
					foreach my $key(keys %{$works{$worker}{'0910'}}){
						$workname =$works{$worker}{'0910'}{$key}{'workname'};
					
						print $fh "$workname ".join(',',@{$works{$worker}{'0910'}{$key}{'msr'}})."\n";
						$tot_attend += $works{$worker}{'0910'}{$key}{'tot_attend'};
						$tot_paid   += $works{$worker}{'0910'}{$key}{'tot_paid'};
					}
					print $fh  "</font>	 </td> \n";
					print $fh "<td><font size=-1>";

					print $fh "		$tot_attend\n";
					print $fh "	 </font></td> \n";
					print $fh "<td><font size=-1>";
					print $fh "		$tot_paid\n";
					print $fh "	</font> </td> \n";
					print $fh "</tr> \n";
					#print $fh "</td>";
					#print $fh "</tr>";
					$tot_attend=$tot_paid=0;

					
				} #if 
				#@mustrolls=();
				$count++;
		}# foreach worker
		print $fh "</table>";
		print $fh "</body></html>";
		close $fh;
		%works=();
		$count=1;
		#exit;

	}#foreach panchayat
  }#foreach block
}#foreach district

sub sortworkercode {

 if ($a  =~ /\/(.+)$/) {
$a_stripped=$1;
}
if ($b  =~ /\/(.+)$/) {
$b_stripped=$1;
}
return $a_stripped <=> $b_stripped;

}
